/**
 * Copyright (c) 2002-2011 旭鸣信息
 * All rights reserved. 
 * File：OrderDaoImpl.java
 * History:
 *       Apr 16, 2012: Initially created, liuzhi.
 */
package com.xteam.ecsystem.dao.Impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.xteam.ecsystem.dao.ICouponDao;
import com.xteam.ecsystem.dao.IFrontLogCouponDao;
import com.xteam.ecsystem.dao.IOrderCouponDao;
import com.xteam.ecsystem.dao.IOrderDao;
import com.xteam.ecsystem.dao.IUserCouponDao;
import com.xteam.ecsystem.entities.BaseCouponInfo;
import com.xteam.ecsystem.entities.BaseDistributeRegion;
import com.xteam.ecsystem.entities.BaseGoodsAttribute;
import com.xteam.ecsystem.entities.BaseGoodsBrand;
import com.xteam.ecsystem.entities.BaseGoodsGroup;
import com.xteam.ecsystem.entities.BaseGoodsInfo;
import com.xteam.ecsystem.entities.BaseMemberBase;
import com.xteam.ecsystem.entities.BaseMemeberCoupon;
import com.xteam.ecsystem.entities.BaseOrderGoods;
import com.xteam.ecsystem.entities.BaseOrderInfo;
import com.xteam.ecsystem.entities.BaseOrderUsingcoupon;
import com.xteam.ecsystem.entities.BaseTypeAttribute;
import com.xteam.ecsystem.entities.LogOrderLog;
import com.xteam.ecsystem.utils.ConstantLocation;
import com.xteam.ecsystem.utils.OrdersQueryCenterStation;
import com.xteam.ecsystem.utils.PageCenterStation;
import com.xteam.ecsystem.utils.StringUtil;

/**
 * 订单信息数据访问实现类
 */
@Repository
@SuppressWarnings("unchecked")
public class OrderDaoImpl extends DDLPrefix implements IOrderDao {
	
	@Autowired
	private BaseDaoImpl baseDao;
	
	@Autowired
	private IOrderCouponDao orderCouponDao;
	
	@Autowired
	private IUserCouponDao userCouponDao;
	
	@Autowired
	private ICouponDao couponDao;
	
	@Autowired
	private IFrontLogCouponDao iflcDao;
	
	public String getAllOrderList(String direction, OrdersQueryCenterStation orderStation, 
			int pageIndex, int pageSize){
		JSONObject jo = new JSONObject();
		String sql = new String("");
		String countSql = new String("");
		List values = new ArrayList();
		
		try {
			sql = "select boi.boi_id as boiId, boi.boi_member as boiMember, boi.boi_osn as boiOsn, " 
				+ "boi.boi_order as boiOrder, boi.boi_pay as boiPay, boi.boi_ship as boiShip, " 
				+ "boi.boi_country as boiCountry, boi.boi_province as boiProvince, boi.boi_city " 
				+ "as boiCity, boi.boi_district as boiDistrict, boi.boi_consignee as boiConsignee, " 
				+ "boi.boi_detail as boiDetail, boi.boi_zipcode as boiZipcode, boi.boi_mobile as " 
				+ "boiMobile, boi.boi_phone as boiPhone, boi.boi_best as boiBest, boi.boi_otime as " 
				+ "boiOtime, boi.boi_ptime as boiPtime, boi.boi_stime as boiStime, boi.boi_ctime " 
				+ "as boiCtime, boi.boi_gpay as boiGpay, boi.boi_couponpay as boiCouponpay, " 
				+ "boi.boi_spay as boiSpay, boi.boi_tpay as boiTpay, " 
				+ "boi.boi_uremark as boiUremark, boi.boi_invsernum as boiInvsernum, " 
				+ "boi.boi_invnum as boiInvnum, boi.boi_creason as boiCreason, boi.boi_isrefund as " 
				+ "boiIsrefund, boi.boi_refundremark as boiRefundremark, boi.boi_deliverymode as " 
				+ "boiDeliverymode, boi.boi_paymode as boiPaymode, boi.boi_paymodename as " 
				+ "boiPaymodename, bmb.bmb_nick as bmbNick " 
				+ "from base_order_info boi left join base_member_base bmb on boi.boi_member=bmb.bmb_id " 
				+ "where 1=1 ";
			
			countSql = "select count(*) from base_order_info boi left join base_member_base bmb " 
				+ "on boi.boi_member=bmb.bmb_id where 1=1 ";
			
			String[] sqlArray = getQueryCondition(sql, countSql, direction, orderStation, values);
			
			PageCenterStation<?> p = (PageCenterStation<?>) this.baseDao
				.queryWithSQL(BaseOrderInfo.class, sqlArray[1], sqlArray[0], 
						null, values.toArray(), pageIndex * pageSize, pageSize);
			
			jo = JSONObject.fromObject(p);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return jo.toString();
	}
	
	private String[] getQueryCondition(String sql, String countSql, String direction, 
			OrdersQueryCenterStation orderStation, List values){
		int boiOrder, boiPay, boiShip;

		if (StringUtil.isNotNullOrEmpty(orderStation)) {
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiInvsernum())) {
				sql += SQL_AND + "boi.boi_invsernum like ?";
				countSql += SQL_AND + "boi.boi_invsernum like ?";
				values.add("%" + orderStation.getBoiInvsernum() + "%");
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiOsn())) {
				sql += SQL_AND + "boi.boi_osn like ?";
				countSql += SQL_AND + "boi.boi_osn like ?";
				values.add("%" + orderStation.getBoiOsn() + "%");
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiConsignee())) {
				sql += SQL_AND + "boi.boi_consignee like ?";
				countSql += SQL_AND + "boi.boi_consignee like ?";
				values.add("%" + orderStation.getBoiConsignee() + "%");
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiOrder())) {
				boiOrder = Integer.parseInt(orderStation.getBoiOrder());
				if (boiOrder != -1) {
					sql += SQL_AND + "boi.boi_order=?";
					countSql += SQL_AND + "boi.boi_order=?";
					values.add(boiOrder);
				}
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiPay())) {
				boiPay = Integer.parseInt(orderStation.getBoiPay());
				if (boiPay != -1) {
					sql += SQL_AND + "boi.boi_pay=?";
					countSql += SQL_AND + "boi.boi_pay=?";
					values.add(boiPay);
				}
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiShip())) {
				boiShip = Integer.parseInt(orderStation.getBoiShip());
				if (boiShip != -1) {
					sql += SQL_AND + "boi.boi_ship=?";
					countSql += SQL_AND + "boi.boi_ship=?";
					values.add(boiShip);
				}
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiMember())) {
				sql += SQL_AND + "boi.boi_member in(select bmb2.bmb_id from base_member_base " 
						+ " as bmb2 where bmb2.bmb_nick like ?)";
				countSql += SQL_AND + "boi.boi_member in(select bmb2.bmb_id from base_member_base " 
						+ " as bmb2 where bmb2.bmb_nick like ?)";
				values.add("%" + orderStation.getBoiMember() + "%");
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiMobile())) {
				sql += SQL_AND + "boi.boi_mobile=?";
				countSql += SQL_AND + "boi.boi_mobile=?";
				values.add(orderStation.getBoiMobile());
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiPhone())) {
				sql += SQL_AND + "boi.boi_phone=?";
				countSql += SQL_AND + "boi.boi_phone=?";
				values.add(orderStation.getBoiPhone());
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiDetail())) {
				sql += SQL_AND + "boi.boi_detail like ?";
				countSql += SQL_AND + "boi.boi_detail like ?";
				values.add("%" + orderStation.getBoiDetail() + "%");
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getOrderBeginTime())) {
				sql += SQL_AND + "boi.boi_otime >=?";
				countSql += SQL_AND + "boi.boi_otime >=?";
				values.add(StringUtil.strToDate(orderStation.getOrderBeginTime(), null));
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getOrderEndTime())) {
				sql += SQL_AND + "boi.boi_otime <=?";
				countSql += SQL_AND + "boi.boi_otime <=?";
				values.add(StringUtil.strToDate(orderStation.getOrderEndTime(), null));
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiCountry())) {
				if (!"-1".equals(orderStation.getBoiCountry())) {
					sql += SQL_AND + "boi.boi_country=?";
					countSql += SQL_AND + "boi.boi_country=?";
					values.add(orderStation.getBoiCountry());
				}
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiProvince())) {
				if (!"-1".equals(orderStation.getBoiProvince())) {
					sql += SQL_AND + "boi.boi_province=?";
					countSql += SQL_AND + "boi.boi_province=?";
					values.add(orderStation.getBoiProvince());
				}
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiCity())) {
				if (!"-1".equals(orderStation.getBoiCity())) {
					sql += SQL_AND + "boi.boi_city=?";
					countSql += SQL_AND + "boi.boi_city=?";
					values.add(orderStation.getBoiCity());
				}
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiDistrict())) {
				if (!"-1".equals(orderStation.getBoiDistrict())) {
					sql += SQL_AND + "boi.boi_district=?";
					countSql += SQL_AND + "boi.boi_district=?";
					values.add(orderStation.getBoiDistrict());
				}
			}
		}
		
		if (StringUtil.isNotNullOrEmpty(direction)) {
			//查询发货单列表，只加载配送状态为【“发货中”和“已发货”】的商品
			if(direction.equals("invoiceList")){
				sql += SQL_AND + "boi.boi_invsernum !=?" + SQL_AND + "boi.boi_ship in(2,3)";
				countSql += SQL_AND + "boi.boi_invsernum !=?" + SQL_AND + "boi.boi_ship in(2,3)";
				values.add("");
			}
		}
		
		if (true) {
			// 订单排序
			sql += " order by boi.boi_otime desc ";
		}
		
		return new String[] {sql, countSql};
	}
	
	public String getOrderList(String direction, OrdersQueryCenterStation orderStation, 
			int pageIndex, int pageSize){
		JSONObject jo = new JSONObject();
		
		try {
			List list = new ArrayList();
			String hql = new String("");
			String countHQL = new String("");
			
			String[] hqlArray = queryCondition(hql, countHQL, direction, orderStation, list);
			PageCenterStation<?> p = (PageCenterStation<?>) this.baseDao
					.queryList(hqlArray[0], hqlArray[1], list.toArray(),
							pageIndex * pageSize, pageSize);
			jo = JSONObject.fromObject(p);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return jo.toString();
	}
	
	private String[] queryCondition(String hql, String countHQL, String direction, 
			OrdersQueryCenterStation orderStation, List list) {
		int boiOrder, boiPay, boiShip;
		
		hql = SQL_SELECT + "new " + BaseOrderInfo.REF + " (boi.boiId, boi.boiMember, boi.boiOsn, "
			+ "boi.boiOrder, boi.boiPay, boi.boiShip, boi.boiCountry, boi.boiProvince, boi.boiCity, "
			+ "boi.boiDistrict, boi.boiConsignee, boi.boiDetail, boi.boiZipcode, "
			+ "boi.boiMobile, boi.boiPhone, boi.boiBest, boi.boiOtime, boi.boiPtime, boi.boiStime, "
			+ "boi.boiCtime, boi.boiGpay, boi.boiCouponpay, boi.boiSpay, boi.boiTpay, boi.boiUremark, "
			+ "boi.boiInvsernum, boi.boiInvnum, boi.boiCreason, boi.boiIsrefund, boi.boiRefundremark, "
			+ "bmb.bmbNick)" + SQL_FROM + BaseOrderInfo.REF + " as boi," + BaseMemberBase.REF 
			+ " as bmb" + SQL_WHERE + " boi.boiMember = bmb.bmbId ";
		
		countHQL = SQL_SELECT + "count(*)" + SQL_FROM + BaseOrderInfo.REF + " as boi," 
			+ BaseMemberBase.REF + " as bmb" + SQL_WHERE + " boi.boiMember = bmb.bmbId ";
		
		if (StringUtil.isNotNullOrEmpty(orderStation)) {
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiInvsernum())) {
				hql += SQL_AND + "boi.boiInvsernum=?";
				countHQL += SQL_AND + "boi.boiInvsernum=?";
				list.add(orderStation.getBoiInvsernum());
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiOsn())) {
				hql += SQL_AND + "boi.boiOsn=?";
				countHQL += SQL_AND + "boi.boiOsn=?";
				list.add(orderStation.getBoiOsn());
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiConsignee())) {
				hql += SQL_AND + "boi.boiConsignee like ?";
				countHQL += SQL_AND + "boi.boiConsignee like ?";
				list.add("%" + orderStation.getBoiConsignee() + "%");
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiOrder())) {
				boiOrder = Integer.parseInt(orderStation.getBoiOrder());
				if (boiOrder != -1) {
					hql += SQL_AND + "boi.boiOrder=?";
					countHQL += SQL_AND + "boi.boiOrder=?";
					list.add(boiOrder);
				}
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiPay())) {
				boiPay = Integer.parseInt(orderStation.getBoiPay());
				if (boiPay != -1) {
					hql += SQL_AND + "boi.boiPay=?";
					countHQL += SQL_AND + "boi.boiPay=?";
					list.add(boiPay);
				}
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiShip())) {
				boiShip = Integer.parseInt(orderStation.getBoiShip());
				if (boiShip != -1) {
					hql += SQL_AND + "boi.boiShip=?";
					countHQL += SQL_AND + "boi.boiShip=?";
					list.add(boiShip);
				}
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiMember())) {
				hql += SQL_AND + "boi.boiMember in(select bmb2.bmbId from "+ BaseMemberBase.REF  
						+ " as bmb2 " + SQL_WHERE + "bmb2.bmbNick like ?)";
				countHQL += SQL_AND + "boi.boiMember in(select bmb2.bmbId from "+ BaseMemberBase.REF 
						+ " as bmb2 " + SQL_WHERE + "bmb2.bmbNick like ?)";
				list.add("%" + orderStation.getBoiMember() + "%");
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiMobile())) {
				hql += SQL_AND + "boi.boiMobile=?";
				countHQL += SQL_AND + "boi.boiMobile=?";
				list.add(orderStation.getBoiMobile());
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiPhone())) {
				hql += SQL_AND + "boi.boiPhone=?";
				countHQL += SQL_AND + "boi.boiPhone=?";
				list.add(orderStation.getBoiPhone());
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiDetail())) {
				hql += SQL_AND + "boi.boiDetail like ?";
				countHQL += SQL_AND + "boi.boiDetail like ?";
				list.add("%" + orderStation.getBoiDetail() + "%");
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getOrderBeginTime())) {
				hql += SQL_AND + "boi.boiOtime >=?";
				countHQL += SQL_AND + "boi.boiOtime >=?";
				list.add(StringUtil.strToDate(orderStation.getOrderBeginTime(), null));
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getOrderEndTime())) {
				hql += SQL_AND + "boi.boiOtime <=?";
				countHQL += SQL_AND + "boi.boiOtime <=?";
				list.add(StringUtil.strToDate(orderStation.getOrderEndTime(), null));
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiCountry())) {
				if (!"-1".equals(orderStation.getBoiCountry())) {
					hql += SQL_AND + "boi.boiCountry=?";
					countHQL += SQL_AND + "boi.boiCountry=?";
					list.add(orderStation.getBoiCountry());
				}
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiProvince())) {
				if (!"-1".equals(orderStation.getBoiProvince())) {
					hql += SQL_AND + "boi.boiProvince=?";
					countHQL += SQL_AND + "boi.boiProvince=?";
					list.add(orderStation.getBoiProvince());
				}
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiCity())) {
				if (!"-1".equals(orderStation.getBoiCity())) {
					hql += SQL_AND + "boi.boiCity=?";
					countHQL += SQL_AND + "boi.boiCity=?";
					list.add(orderStation.getBoiCity());
				}
			}
			
			if (StringUtil.isNotNullOrEmpty(orderStation.getBoiDistrict())) {
				if (!"-1".equals(orderStation.getBoiDistrict())) {
					hql += SQL_AND + "boi.boiDistrict=?";
					countHQL += SQL_AND + "boi.boiDistrict=?";
					list.add(orderStation.getBoiDistrict());
				}
			}
		}
		
		if (StringUtil.isNotNullOrEmpty(direction)) {
			//查询发货单列表，只加载配送状态为【“发货中”和“已发货”】的商品
			if(direction.equals("invoiceList")){
				hql += SQL_AND + "boi.boiInvsernum !=?" + SQL_AND + "boi.boiShip in(2,3)";
				countHQL += SQL_AND + "boi.boiInvsernum !=?" + SQL_AND + "boi.boiShip in(2,3)";
				list.add("");
			}
		}
		
		if (true) {
			// 订单排序
			hql += " order by boi.boiOtime desc ";
		}
		
		return new String[] {countHQL, hql};
	}
	
	public String getRegionInfo(String regionId){
		String hql = "";
		List list = new ArrayList();
		
		try {
			hql = SQL_FROM + BaseDistributeRegion.REF + SQL_WHERE + "bdrId=?";
			list = this.baseDao.find(hql, new Object[]{regionId});
			if (!list.isEmpty()) {
				BaseDistributeRegion region = (BaseDistributeRegion)list.get(0);
				return JSONObject.fromObject(region).toString();
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return "";
	}

	public String getOrderInfoById(String id){
		JSONObject jo = new JSONObject();
		List list = new ArrayList();
		
		try {
			String hql = SQL_FROM + BaseOrderInfo.REF + SQL_WHERE + "boiId=?";
			list = this.baseDao.find(hql, new Object[]{id});
			
			if (!list.isEmpty()) {
				jo = JSONObject.fromObject((BaseOrderInfo)list.get(0));
				return jo.toString();
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return "";
	}
	
	public String getMemberInfoById(String id){
		JSONObject jo = new JSONObject();
		try {
			List list = new ArrayList();
			
			String hql = SQL_FROM + BaseMemberBase.REF + SQL_WHERE + "bmbId=?";
			list = this.baseDao.find(hql, new Object[]{id});
			
			if (!list.isEmpty()) {
				jo = JSONObject.fromObject((BaseMemberBase)list.get(0));
				return jo.toString();
			}
			

		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return "";
	}
	
	private JSONObject getGoodsResById(String id ,String bogId){
		try {
			List list = new ArrayList();
			
			String hql = SQL_SELECT + "new " + BaseOrderGoods.REF + " (bog.bogId, bog.bogOid, bog.bogGid, "
					+ "bog.bogName, bog.bogMarket, bog.bogPrice, bog.bogAttribute,bog.bogAttprice, bog.bogNumber, "
					+ "bog.bogTotal, bgb.bgbName, bgi.bgiGsn, bgi.bgiNumber)" + SQL_FROM 
					+ BaseOrderGoods.REF + " bog," 
					+ BaseGoodsBrand.REF + " bgb," 
					+ BaseGoodsInfo.REF + " bgi"
					+ SQL_WHERE + "bog.bogGid = bgi.bgiId" + SQL_AND + "bgb.bgbId = bgi.bgiBrand" 
					+ SQL_AND + "bog.bogGid = ?" + SQL_AND + "bog.bogId = ?";
			list = this.baseDao.find(hql, new Object[]{id,bogId});
			
			if (!list.isEmpty()) {
				return JSONObject.fromObject((BaseOrderGoods)list.get(0));
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	public String getOrderGoodsById(String id){
		JSONArray ja = new JSONArray();
		try {
			List<BaseOrderGoods> list = new ArrayList<BaseOrderGoods>();
			
			String hql = SQL_FROM + BaseOrderGoods.REF + SQL_WHERE + "bogOid=? order by bogTotal";
			list = this.baseDao.find(hql, new Object[]{id});
			
			if (!list.isEmpty()) {
				for (BaseOrderGoods orderGoods : list) {
					ja.add(getGoodsResById(orderGoods.getBogGid(),orderGoods.getBogId()));
				}
				
				return ja.toString();
			}
			

		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return "";
	}
	
	/**
	 * 订单处理，未涉及线上交易。若涉及线上交易，则需修改此方法。
	 * 
	 * @see com.xteam.ecsystem.dao.IOrderDao#saveDisposedOrder(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
	 */
	public BaseOrderInfo saveDisposedOrder(String id, String disposeType, String boiCreason,
			String boiIsrefund, String boiRefundremark, String boiInvnum){
		try {
			// 【收货】操作
			if (ConstantLocation.BTN_SHOU_H[0].equals(disposeType)) {
				int status = this.baseDao.queryProcedureForInt("take_delivery_goods", new Object[]{id});
				if(status == 1){
					return (BaseOrderInfo)this.baseDao.get(BaseOrderInfo.class, id);
				}else{
					return null;
				}
			}else{
				BaseOrderInfo order = (BaseOrderInfo)this.baseDao.get(BaseOrderInfo.class, id);
				
				// 【确认】操作
				if (ConstantLocation.BTN_QUE_R[0].equals(disposeType)) {
					order.setBoiOrder(1);	
				}
				// 【付款】操作
				else if(ConstantLocation.BTN_FU_K[0].equals(disposeType)){
					order.setBoiOrder(1);
					order.setBoiPay(1);
					order.setBoiPtime(new Date());
				}
				// 【配货】操作
				else if(ConstantLocation.BTN_PEI_H[0].equals(disposeType)){
					order.setBoiShip(1);
				}
				// 【发货单】操作
				else if(ConstantLocation.BTN_FA_H_D[0].equals(disposeType)){
					order.setBoiShip(2);
					order.setBoiInvsernum(StringUtil.getSerTimeNum());
					order.setBoiStime(null);
					order.setBoiCtime(null);
					order.setBoiInvnum(null);
				}
				// 【去发货】操作
				else if(ConstantLocation.BTN_QU_F_H[0].equals(disposeType)){
					order.setBoiShip(3);
					order.setBoiStime(new Date());
					order.setBoiInvnum(boiInvnum);
				}
				// 【退货】操作
				else if(ConstantLocation.BTN_TUI_H[0].equals(disposeType)){
					order.setBoiShip(5);
				}
				// 【取消】操作
				else if(ConstantLocation.BTN_QU_X[0].equals(disposeType)){
					// 包含付款前取消和付款后取消
					int isrefund = Integer.parseInt(boiIsrefund);
					if(isrefund == 1) {
						order.setBoiPay(2);
						// TODO 退款
					}
					
					order.setBoiOrder(2);
					order.setBoiCreason(boiCreason);
					order.setBoiIsrefund(isrefund);
					order.setBoiRefundremark(boiRefundremark);
					
					// 若会员使用了购物券，则退回订单中使用的购物券给会员
					returnOrderCoupon(id, order.getBoiMember());
				}
				// 【无法联系】操作
				else if(ConstantLocation.BTN_WU_F_L_X[0].equals(disposeType)){
					order.setBoiShip(6);
				}
				// 【失效】操作
				else if(ConstantLocation.BTN_SHI_X[0].equals(disposeType)){
					order.setBoiOrder(4);
					order.setBoiPay(2);
					// TODO 退款
					order.setBoiStime(null);
					
					// 若会员使用了购物券，则退回订单中使用的购物券给会员
					returnOrderCoupon(id, order.getBoiMember());
				}
				
				this.baseDao.update(order);
				return order;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 若会员使用了购物券，则退回订单中使用的购物券给会员
	 * @author liuzhi 2014-2-18
	 * @param orderId
	 * @param memberId
	 */
	private void returnOrderCoupon(String orderId, String memberId){
		try {
			// a-读取此订单中使用的购物券
			BaseOrderUsingcoupon bouc = this.orderCouponDao.getBoucInfo(orderId);
			
			if (bouc != null) {
				// b-读取用户购物券
				BaseMemeberCoupon bmc = this.userCouponDao.getBmcInfo(memberId, bouc.getBouCoupon());
				if(bmc != null){
					bmc.setBmcNumber(bmc.getBmcNumber() + bouc.getBouNumber());
					this.userCouponDao.updateBmcInfo(bmc);
				}else{
					bmc = new BaseMemeberCoupon();
					bmc.setBmcMember(memberId);
					bmc.setBmcCoupon(bouc.getBouCoupon());
					
					BaseCouponInfo bci = this.couponDao.getCouponInfo(bouc.getBouCoupon());
					bmc.setBmcName(bci.getBciName());
					bmc.setBmcNumber(bouc.getBouNumber());
					bmc.setBmcPicurl(bci.getBciPicurl());
					bmc.setBmcPrice(bci.getBciPrice());
					this.userCouponDao.saveBmcInfo(bmc);
				}
				
				// c-删除此订单中使用的购物券
				this.orderCouponDao.deleteOrderUsingcoupon(bouc);
				
				// d-插入返还购物券日志记录
				this.iflcDao.saveCouponLog(memberId, bouc.getBouCoupon(), bouc.getBouPrice(), +1, "返还购物券，订单：" + orderId);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void saveQueRenOrder(String ids){
		try {
			String oids[] = ids.split(",");
			for (String id : oids) {
				Object obj = this.baseDao.get(BaseOrderInfo.class, id);
				if(obj != null){
					BaseOrderInfo order = (BaseOrderInfo)obj;
					order.setBoiOrder(1);		
					this.baseDao.update(order);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void saveQuXiaoOrder(String ids, String boiCreason, String boiIsrefund, 
			String boiRefundremark){
		int isrefund = Integer.parseInt(boiIsrefund);
		
		try {
			String oids[] = ids.split(",");
			for (String id : oids) {
				Object obj = this.baseDao.get(BaseOrderInfo.class, id);
				if(obj != null){
					BaseOrderInfo order = (BaseOrderInfo)obj;
					if(isrefund == 1) order.setBoiPay(2);
					order.setBoiOrder(2);
					order.setBoiCreason(boiCreason);
					order.setBoiIsrefund(isrefund);
					order.setBoiRefundremark(boiRefundremark);	
					
					this.baseDao.update(order);
					
					// 若会员使用了购物券，则退回订单中使用的购物券给会员
					returnOrderCoupon(id, order.getBoiMember());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private void delBogInfoByOrderId(String orderId){
		try {
			String hql = SQL_DELETE + SQL_FROM + BaseOrderGoods.REF + SQL_WHERE + "bogOid=?";
			this.baseDao.executeUpdate(hql, new Object[]{orderId});
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private void delLolInfoByOrderId(String orderId){
		try {
			String hql = SQL_DELETE + SQL_FROM + LogOrderLog.REF + SQL_WHERE + "lolOrder=?";
			this.baseDao.executeUpdate(hql, new Object[]{orderId});
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private void delBoiInfoByOrderId(String orderId){
		try {
			String hql = SQL_DELETE + SQL_FROM + BaseOrderInfo.REF + SQL_WHERE + "boiId=?";
			this.baseDao.executeUpdate(hql, new Object[]{orderId});
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void delOrdersByIds(String ids){
		try {
			String oids[] = ids.split(",");
			for (String id : oids) {
				Object obj = this.baseDao.get(BaseOrderInfo.class, id);
				if(obj != null){
					delLolInfoByOrderId(id);
					delBogInfoByOrderId(id);
					delBoiInfoByOrderId(id);
				}
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public String getRegionInfoList(String bdrId, String bdrParent){
		List list = new ArrayList();
		String hql = "";
		
		try {
			hql = SQL_FROM + BaseDistributeRegion.REF + SQL_WHERE + "1=1";
			
			if(StringUtil.isNotNullOrEmpty(bdrId)){
				hql += SQL_AND + "bdrId=?";
				list = this.baseDao.find(hql, new Object[]{bdrId});
			}else if(StringUtil.isNotNullOrEmpty(bdrParent)){
				hql += SQL_AND + "bdrParent=?";
				list = this.baseDao.find(hql, new Object[]{bdrParent});				
			}else{
				list = this.baseDao.find(hql);		
			}
			
			if (!list.isEmpty()) {
				return JSONArray.fromObject(list).toString();
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return "";
	}
	
	public int updateConsigneeInfo(BaseOrderInfo order){
		int count = 0;
		String hql = "";
		
		try {
			hql = SQL_UPDATE + BaseOrderInfo.REF + " set boiConsignee=?,boiDetail=?,"
					+ "boiZipcode=?,boiMobile=?,boiPhone=?,boiBest=?,boiCountry=?,boiProvince=?,"
					+ "boiCity=?,boiDistrict=?,boiUremark=?" + SQL_WHERE + "boiId=?";
			
			count = this.baseDao.executeUpdate(hql, new Object[]{order.getBoiConsignee(), 
					order.getBoiDetail(), order.getBoiZipcode(), 
					order.getBoiMobile(),order.getBoiPhone(), order.getBoiBest(), 
					order.getBoiCountry(),order.getBoiProvince(), order.getBoiCity(), 
					order.getBoiDistrict(),order.getBoiUremark(), order.getBoiId()});
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
		
		return count;
	}
	
	public String[] setParam(String hql, String countHQL, String boiConsignee,
			String shopname, String boiMember, String boiMobile,
			String boiOrder, String boiOtimes, String boiOtimee,
			String boiShip, List list) {
		hql = SQL_FROM + "BaseOrderInfo" + SQL_WHERE + " 1=1 ";
		countHQL = SQL_SELECT + " count(*) " + SQL_FROM + "BaseOrderInfo"
				+ SQL_WHERE + " 1=1 ";

		if (boiConsignee != null && boiConsignee.length() > 0
				&& !boiConsignee.equals("undefined")
				&& !boiConsignee.equals("null")) {
			hql += SQL_AND + " boiConsignee " + SQL_LIKE + " ? ";
			countHQL += SQL_AND + " boiConsignee " + SQL_LIKE + " ? ";
			list.add("%" + boiConsignee + "%");
		}

		if (boiMobile != null && boiMobile.length() > 0
				&& !boiMobile.equals("undefined") && !boiMobile.equals("null")) {
			hql += SQL_AND + " boiOrderMobile " + SQL_LIKE + " ? ";
			countHQL += SQL_AND + " boiOrderMobile " + SQL_LIKE + " ? ";
			list.add("%" + boiMobile + "%");
		}
		if (boiShip != null && boiShip.length() > 0
				&& !boiShip.equals("undefined") && !boiShip.equals("null")&&!"-1".equals(boiShip)) {
			hql += SQL_AND + " boiShip =? ";
			countHQL += SQL_AND + " boiShip=? ";
			list.add(Integer.parseInt(boiShip));
		}
		if (shopname != null && shopname.length() > 0
				&& !shopname.equals("undefined") && !shopname.equals("null")) {
			hql += SQL_AND
					+ " boiId in (select bogOid from BaseOrderGoods where bogName like ?) ";
			countHQL += SQL_AND
					+ " boiId in (select bogOid from BaseOrderGoods where bogName like ?) ";
			list.add("%" + shopname + "%");
		}
		if (boiMember != null && boiMember.length() > 0
				&& !boiMember.equals("undefined") && !boiMember.equals("null")) {
			hql += SQL_AND + " boiMember " + SQL_LIKE + " ? ";
			countHQL += SQL_AND + " boiMember " + SQL_LIKE + " ? ";
			list.add("%" + boiMember + "%");
		}

		if (boiOtimes != null && boiOtimes.length() > 0
				&& !boiOtimes.equals("undefined") && !boiOtimes.equals("null")) {
			hql += " and  boiOtime>=?";
			countHQL += " and  boiOtime>=?";
			list.add(StringUtil.strToDate(boiOtimes, null));
		}
		if (boiOrder != null && boiOrder.length() > 0
				&& !boiOrder.equals("undefined") && !boiOrder.equals("null")
				&& !boiOrder.equals("-1")) {
			hql += SQL_AND + " boiOrder=? ";
			countHQL += SQL_AND + " boiOrder=? ";
			list.add(Integer.parseInt(boiOrder));
		}

		if (boiOtimee != null && boiOtimee.length() > 0
				&& !boiOtimee.equals("undefined") && !boiOtimee.equals("null")) {
			hql += " and  boiOtime<=?";
			countHQL += " and  boiOtime<=?";
			list.add(StringUtil.strToDate(boiOtimee, null));
		}
		hql += " order by boiOtime desc";
		return new String[] { hql, countHQL };

	}

	@Override
	public String findOrderList(int pageIndex, int pageSize,
			String boiConsignee, String shopname, String boiMember,
			String boiMobile, String boiOrder, String boiOtimes,
			String boiOtimee, String boiShip) {
		JSONObject jo = new JSONObject();
		try {
			List list = new ArrayList();
			String hql = new String("");
			String countHQL = new String("");
			String[] sqls = setParam(hql, countHQL, boiConsignee, shopname,
					boiMember, boiMobile, boiOrder, boiOtimes, boiOtimee,
					boiShip, list);
			PageCenterStation<?> p = (PageCenterStation<?>) this.baseDao
					.queryList(sqls[1], sqls[0], list.toArray(),
							pageIndex * pageSize, pageSize);
			jo = JSONObject.fromObject(p);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return jo.toString();
	}

	@Override
	public List findExportOrder(String boiConsignee, String shopname,
			String boiMember, String boiMobile, String boiOrder,
			String boiOtimes, String boiOtimee, String boiShip) {
		try {
			List list = new ArrayList();
			String hql = "";
			String countHQL = "";
			String[] sqls = setParam(hql, countHQL, boiConsignee, shopname,
					boiMember, boiMobile, boiOrder, boiOtimes, boiOtimee,boiShip, list);
			return baseDao.queryHqlLis(sqls[0], list.toArray());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public String findOrderProductList(int pageIndex, int pageSize,
			String orderid) {
		JSONObject jo = new JSONObject();
		try {
			List list = new ArrayList();
			String hql = SQL_FROM + "BaseOrderGoods" + SQL_WHERE + " 1=1 ";
			String countHQL = SQL_SELECT + " count(*) " + SQL_FROM
					+ "BaseOrderGoods" + SQL_WHERE + " 1=1 ";
			if (orderid != null && orderid.length() > 0
					&& !orderid.equals("undefined") && !orderid.equals("null")) {
				hql += SQL_AND + " bogOid=?";
				countHQL += SQL_AND + " bogOid=?";
				list.add(orderid);
			}
			PageCenterStation<?> p = (PageCenterStation<?>) this.baseDao
					.queryList(countHQL, hql, list.toArray(), pageIndex * pageSize, pageSize);
			jo = JSONObject.fromObject(p);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return jo.toString();
	}

	@Override
	public void updateOrderState(String boiId, String currentState,
			String optionChange) {
		String l[] = optionChange.split("=");
		String sql = "update  base_order_info set boi_order="
				+ Integer.parseInt(l[0]) + ", boi_ship="
				+ Integer.parseInt(l[1]) + " where boi_id='" + boiId + "'";
		baseDao.executeUpdateWithSQL(sql, (Object[])null);
	}

	@Override
	public List getOrderList(String orderlist, List listin) {
		return baseDao.queryHqlIn(orderlist, listin);
	}

	@Override
	public String getGoodsCombobox(String value) {
		JSONArray jarray = new JSONArray();
		List list = new ArrayList();
		try {
			String hql = SQL_FROM + BaseGoodsInfo.REF + SQL_WHERE + " (bgiName like '%" +value+ "%') or (bgiGsn like '%"+value+"%')";
			list = this.baseDao.find(hql);
			jarray = JSONArray.fromObject(list);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return jarray.toString();
	}

	@Override
	public String showGoodsInfoById(String bgiId) {
		if (bgiId != null) {
			List list = new ArrayList();
			String hql = SQL_SELECT + " new " + BaseGoodsInfo.REF
					+ " (bgi.bgiId,bgi.bgiName,bgi.bgiGsn,bgi.bgiGroup,bgi.bgiBrand,bgi.bgiPrice,bgi.bgiMarket,bgg.bggName,bgb.bgbName)" 
					+ SQL_FROM + BaseGoodsInfo.REF + " as bgi, "
					+ BaseGoodsGroup.REF + " as bgg, "
					+ BaseGoodsBrand.REF + " as bgb "+ SQL_WHERE
					+ " bgi.bgiGroup = bgg.bggId " + SQL_AND
					+ " bgi.bgiBrand = bgb.bgbId " + SQL_AND
					+ " bgi.bgiId = '"+bgiId+"' ";
			list = this.baseDao.find(hql);
			if (!list.isEmpty()) {
				return JSONArray.fromObject(list.get(0)).toString();
			}else{
				return "";
			}
		} else {
			return null;
		}
	}

	@Override
	public String showGoodsAttrById(String bgiId) {
		if (bgiId != null) {
			List list = new ArrayList();
			
			String hql = SQL_SELECT + " new " + BaseGoodsAttribute.REF
					+ " (bga.bgaId,bga.bgaGoods,bga.bgaGtype,bga.bgaTattri,bga.bgaValue,bga.bgaPrice,bga.bgaOrder,bta.btaName,bta.btaAtype,bta.btaState)" 
					+ SQL_FROM + BaseGoodsAttribute.REF + " as bga, "
					+ BaseTypeAttribute.REF + " as bta "+ SQL_WHERE
					+ " bga.bgaTattri = bta.btaId " + SQL_AND
					+ " bga.bgaGoods = '"+bgiId+"' "
					+ " order by bta.btaOrder,bga.bgaTattri,bga.bgaOrder";
			
			list = this.baseDao.find(hql);
			
			if (!list.isEmpty()) {
				return JSONArray.fromObject(list).toString();
			}else{
				return "";
			}
		} else {
			return null;
		}
	}

	public int saveOrderGoods(String jsonStr,String boiGpay,String boiId) {
		try {
			JSONArray ja = JSONArray.fromObject(jsonStr);
			for (int i = 0,j = ja.size(); i < j; i++){
				JSONObject obj = ja.getJSONObject(i);
				BaseOrderGoods baseOrderGoods = (BaseOrderGoods) JSONObject.toBean(obj,
						BaseOrderGoods.class);
				if(obj.get("_state").equals("added")){
					baseDao.save(baseOrderGoods);
				}else if(obj.get("_state").equals("modified")){
					baseDao.update(baseOrderGoods);
				}else if(obj.get("_state").equals("removed")){
					String hql = SQL_DELETE + SQL_FROM + BaseOrderGoods.REF + SQL_WHERE + "bogId=?";
					this.baseDao.executeUpdate(hql, new Object[]{baseOrderGoods.getBogId()});
				}
			}
			
			//更新订单商品总价
			String obj = getOrderInfoById(boiId);
			JSONObject jo = JSONObject.fromObject(obj);
			Double boiSpay=jo.getDouble("boiSpay");             //获取配送价格
			Double boiTpay=boiSpay+Double.parseDouble(boiGpay); //计算应付总价
			
			String sql = "update  base_order_info set boi_gpay="
				+ Double.parseDouble(boiGpay) + ",boi_tpay=" + boiTpay + " where boi_id='" + boiId + "'";
			
			int count = baseDao.executeUpdateWithSQL(sql, (Object[])null);
			
			return count;
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
	}

}
